#pip install pandas python-docx openpyxl

import pandas as pd
from datetime import datetime
from docx import Document
from docx.shared import Pt, Cm, RGBColor
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml.ns import qn

def generate_word_report():
    # 读取Excel数据
    df = pd.read_excel("/Users/jijiuxue/Downloads/数据分析.xlsx", sheet_name="Sheet7")

    
    # 数据预处理
    df["销售日期"] = pd.to_datetime(df["销售日期"])
    df["月份"] = df["销售日期"].dt.month

    # 计算基础指标
    total_sales = df["销售额（元）"].sum()
    total_quantity = df["销量"].sum()
    avg_price = df["单价（元）"].mean()
    avg_rating = df["客户评价（1-5星）"].mean()

    # 创建Word文档
    doc = Document()
    
    # 设置中文字体
    doc.styles['Normal'].font.name = '微软雅黑'
    doc.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '微软雅黑')
    
    # 添加标题
    title = doc.add_heading(level=0)
    title_run = title.add_run("销售数据分析报告")
    title_run.font.size = Pt(22)
    title_run.font.color.rgb = RGBColor(0, 51, 102)  # 深蓝色
    
    # 添加时间戳
    time_para = doc.add_paragraph()
    time_para.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    time_run = time_para.add_run(f"生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M')}")
    time_run.font.size = Pt(10)
    time_run.font.color.rgb = RGBColor(128, 128, 128)  # 灰色
    
    # 添加总体概况
    doc.add_heading("一、总体销售概况", level=1)
    overview = [
        ("总销售额", f"¥{total_sales:,.0f}"),
        ("总销量", f"{total_quantity} 件"),
        ("平均单价", f"¥{avg_price:.1f}"),
        ("平均客户评分", f"{avg_rating:.2f}/5")
    ]
    
    # 创建带样式的表格
    table = doc.add_table(rows=1, cols=2)
    table.style = 'Light Shading Accent 1'
    hdr_cells = table.rows[0].cells
    hdr_cells[0].text = "指标"
    hdr_cells[1].text = "数值"
    
    for item in overview:
        row_cells = table.add_row().cells
        row_cells[0].text = item[0]
        row_cells[1].text = item[1]
    
    # 核心分析维度
    doc.add_heading("二、核心分析维度", level=1)
    
    # 销售额TOP3
    doc.add_heading("1. 销售额TOP3商品", level=2)
    top_sales = df.sort_values("销售额（元）", ascending=False).head(3)
    sales_table = doc.add_table(top_sales.shape[0]+1, 3)
    sales_table.style = 'Medium Grid 1 Accent 1'
    
    # 设置表头
    sales_table.cell(0,0).text = "排名"
    sales_table.cell(0,1).text = "商品名称"
    sales_table.cell(0,2).text = "销售额"
    
    for i, (_, row) in enumerate(top_sales.iterrows(), 1):
        sales_table.cell(i,0).text = str(i)
        sales_table.cell(i,1).text = row["商品名称"]
        sales_table.cell(i,2).text = f"¥{row['销售额（元）']:,.0f}"
    
    # 销量TOP3
    doc.add_heading("2. 销量TOP3商品", level=2)
    top_quantity = df.sort_values("销量", ascending=False).head(3)
    qty_table = doc.add_table(top_quantity.shape[0]+1, 3)
    qty_table.style = 'Medium Grid 3 Accent 3'
    
    qty_table.cell(0,0).text = "排名"
    qty_table.cell(0,1).text = "商品名称"
    qty_table.cell(0,2).text = "销量"
    
    for i, (_, row) in enumerate(top_quantity.iterrows(), 1):
        qty_table.cell(i,0).text = str(i)
        qty_table.cell(i,1).text = row["商品名称"]
        qty_table.cell(i,2).text = f"{row['销量']}件"
    
    # 价格区间分析
    doc.add_heading("3. 价格区间分析", level=2)
    price_bins = [0, 100, 300, float('inf')]
    labels = ["低价商品（≤¥100）", "中端商品（¥100-300）", "高端商品（>¥300）"]
    price_analysis = df.groupby(pd.cut(df["单价（元）"], bins=price_bins, labels=labels)).agg(
        销量占比=("销量", lambda x: f"{x.sum()/total_quantity:.1%}"),
        销售额占比=("销售额（元）", lambda x: f"{x.sum()/total_sales:.1%}")
    ).reset_index()
    
    # 创建价格分析表格
    price_table = doc.add_table(price_analysis.shape[0]+1, 3)
    price_table.style = 'Light List Accent 2'
    
    price_table.cell(0,0).text = "价格区间"
    price_table.cell(0,1).text = "销量占比"
    price_table.cell(0,2).text = "销售额占比"
    
    for i, (_, row) in enumerate(price_analysis.iterrows(), 1):
        price_table.cell(i,0).text = str(row["单价（元）"])
        price_table.cell(i,1).text = row["销量占比"]
        price_table.cell(i,2).text = row["销售额占比"]
    
    # 业务建议
    doc.add_heading("三、业务建议", level=1)
    recommendations = [
        f"库存优化：建议增加 {df.loc[df['销量'].idxmax(), '商品名称']} 的库存配置",
        f"定价策略：中端价位商品（¥100-300）贡献 {price_analysis.loc[1, '销售额占比']} 销售额，建议重点维护",
        f"地区策略：在 {df.groupby('地区')['销售额（元）'].sum().idxmax()} 地区加大营销投入",
        f"产品改进：需关注 {df.loc[df['客户评价（1-5星）'].idxmin(), '商品名称']} 的客户反馈（评分：{df['客户评价（1-5星）'].min():.1f}）"
    ]
    
    for rec in recommendations:
        p = doc.add_paragraph()
        p.add_run("• ").bold = True
        p.add_run(rec)
        p.paragraph_format.line_spacing = 1.5
    
    # 设置页面格式
    section = doc.sections[0]
    section.left_margin = Cm(2.5)
    section.right_margin = Cm(2.5)
    
    # 保存文档
    doc.save("销售分析报告.docx")
    print("Word报告已生成：销售分析报告.docx")

if __name__ == "__main__":
    generate_word_report()

